﻿using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;

/// <summary>
/// Summary description for Category
/// </summary>
public class Category
{
	public Category()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public static sqlCategoryProvider  master  = new sqlCategoryProvider();

    public static int InsertEncy(int Ancestor, string cTitle , string cPageName,int ctypeId, string cDescription )
    {
        return master.InsertEncy(Ancestor, cTitle, cPageName, ctypeId, cDescription);
    }
    public static List<ListItem> CategoryNumberByAncestor(int Ancestor )
    {
        return master.CategoryNumberByAncestor(Ancestor);
    }
    public static int UpdateName(int catId, string cTitle )
    {
        return master.UpdateName(catId, cTitle);
    }
    public static int UpdateNameDescription(int catId, string cTitle, string  cDescription)
    {
        return master.UpdateNameDescription(catId, cTitle, cDescription);
    }

    public static List<CategoryItem> ByAncestorsAndAlfabet(int Ancestor, string ab) 
    {
        List<CategoryItem> list = new List<CategoryItem>();
        Dal.arParams.Clear();
        Dal.arParams.Add(Ancestor);
        Dal.arParams.Add(ab);
        DataTable DT = Dal.ExecuteDBsp("usp_GetCategoryAncestorsAlfabet", Dal.arParams, true);

        CategoryItem f;
        if (DT != null)
        {
            if (DT.Rows.Count > 0)
                foreach (DataRow DR in DT.Rows)
                {
                    f = new CategoryItem();
                    f.cPageName = Convert.ToString(DR["cPageName"]);
                    f.Ancestor = Convert.ToInt32(DR["Ancestor"]);
                    f.catId = Convert.ToInt32(DR["catId"]);
                    f.cTitle = Convert.ToString(DR["cTitle"]);
                    //f.inSearch = CBool(reader("inSearch"))
                    f.number = Convert.ToInt32(DR["OrderID"]);
                    list.Add(f);
                }
        }
        return list;
    }

    public static bool IfcPageNameExsit(String cPageNam)
    {
        return master.IfcPageNameExsit(cPageNam);
    }
    public static int IfcPageNameExsitNew(string cPageNam)
    {
        return master.IfcPageNameExsitNew(cPageNam);
    }
    public static List<CategoryItem> ByAncestors(int Ancestor)
    {
        return master.ByAncestors(Ancestor);
    }
    public static  List<CategoryItem> ByType(int ctypeId)
    {
        return master.ByType(ctypeId);
    }
    public static List<CategoryItem> MainPage()
    {
        return master.MainPage();
    }
    public static CategoryItem ItemByPageName(string cPageName)
    {
        return master.ItemByPageName(cPageName);
    }
    public static CategoryItem ItemById(int catId)
    {   
        return master.ItemById(catId);
    }
    public static List<ListItem> NumbersList(int catId)
    {
         return master.NumbersList(catId);
    }
    public static int UpdatePageName(int catId, string cPageName)
    {
        return master.UpdatePageName(catId, cPageName);
    }
    public static void Delete(int catId)
    {
        Dal.arParams.Clear();
        Dal.arParams.Add(catId);
        Dal.ExecuteDBsp("Admin_DeleteCategory", Dal.arParams);
    }
    public static int UpdateNumber(int catId, int number)
    {
        return master.UpdateNumber(catId, number);
    }
}

public class sqlCategoryProvider
{
    SqlConnection myConnection;
    SqlCommand myCommand;
    String connectionString = WebConfigurationManager.ConnectionStrings["artcube_co_il_MedConnectionString1"].ToString();

    public List<ListItem> NumbersList(int catId)
{    //OrderBy =0 order by numbers else order by name
        List<ListItem> list = new List<ListItem>();

        myConnection = new SqlConnection(connectionString);
        myCommand =new SqlCommand("usp_GetCategoryNumbersById", myConnection);
    
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@catId", catId);


        try
{
            myConnection.Open();
            SqlDataReader reader = myCommand.ExecuteReader();
            ListItem f ;
            
            if(reader.HasRows)
            {
                while(reader.Read())
                {
                    f = new ListItem();
                    f.Text = Convert.ToString(reader["number"]);
                    f.Value = Convert.ToString(reader["number"]);
                    list.Add(f);

                }
                reader.Close();
                myConnection.Close();
                return list;
            }
            else
            {
                myConnection.Close();
                list = null;
            }

        }
        catch
        {
            myConnection.Close();
            list = null;
        }
        return list;
    }

    public CategoryItem ItemByPageName(string cPageName)
    {
        Dal.arParams.Clear();
        Dal.arParams.Add(cPageName);
        DataTable dt = Dal.ExecuteDBsp("usp_GetCategoryByCPageName", Dal.arParams,true);

        CategoryItem f = null;
        try
        {
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dRow in dt.Rows)
                    {
                        f = new CategoryItem();

                        f.cPageName = Convert.ToString(dRow["cPageName"]);
                        f.Ancestor = Convert.ToInt32(dRow["Ancestor"]);
                        f.catId = Convert.ToInt32(dRow["catId"]);
                        f.cTitle = Convert.ToString(dRow["cTitle"]);
                        f.cDescription = Convert.ToString(dRow["cDescription"]);
                        f.number = Convert.ToInt32(dRow["orderID"]);
                    }
                }
            }
        }
        catch { }
        return f;
    }

    public CategoryItem ItemById(int catId)
    {
        Dal.arParams.Clear();
        Dal.arParams.Add(catId);
        DataTable dt = Dal.ExecuteDBsp("usp_GetCategoryById", Dal.arParams);

        CategoryItem f=null;
        try
        {
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dRow in dt.Rows)
                    {
                        f = new CategoryItem();

                        f.cPageName = Convert.ToString(dRow["cPageName"]);
                        f.Ancestor = Convert.ToInt32(dRow["Ancestor"]);
                        f.catId = Convert.ToInt32(dRow["catId"]);
                        f.cTitle = Convert.ToString(dRow["cTitle"]);
                        f.cDescription = Convert.ToString(dRow["cDescription"]);
                        f.number = Convert.ToInt32(dRow["orderID"]);

                    }
                }
            }
        }
        catch { }
        return f;
    }

    public List<CategoryItem> MainPage()
    {
        List<CategoryItem>list = new List<CategoryItem>();

        myConnection =new SqlConnection(connectionString);
        myCommand =new SqlCommand("usp_GetMainPageCategorys", myConnection);

        myCommand.CommandType = CommandType.StoredProcedure;
        try
        {
            myConnection.Open();
            SqlDataReader reader = myCommand.ExecuteReader();
            CategoryItem f;
            if(reader.HasRows)
            {
                while(reader.Read())
                {
                    f = new CategoryItem();
                    f.cPageName = Convert.ToString(reader["cPageName"]);
                    f.Ancestor = Convert.ToInt32(reader["Ancestor"]);
                    f.catId = Convert.ToInt32(reader["catId"]);
                    f.ctypeId = Convert.ToInt32(reader["ctypeId"]);
                    f.cTitle = Convert.ToString(reader["cTitle"]);
                    //' f.inSearch = CBool(reader("inSearch"))
                    f.number = Convert.ToInt32(reader["number"]);
                    list.Add(f);

                }
                reader.Close();
                myConnection.Close();
                return list;
            }
            else
            {
                myConnection.Close();
                list = null;
            }
        }
        catch
        {
            myConnection.Close();
            list = null;
        }
        return list;
    }

    public List<ListItem> CategoryNumberByAncestor(int Ancestor)
    {
        List<ListItem> list = new List<ListItem>();

        myConnection = new SqlConnection(connectionString);
        myCommand = new SqlCommand("usp_CategoryNumberByAncestor", myConnection);

        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@Ancestor", Ancestor);
        try
        {
            myConnection.Open();
            SqlDataReader reader = myCommand.ExecuteReader();
            ListItem f;
            if(reader.HasRows)
            {
                while(reader.Read())
                {
                    f = new ListItem();
                    f.Text = Convert.ToString(reader["number"]);
                    f.Value = Convert.ToString(reader["number"]);
                    list.Add(f);
                }
                reader.Close();
                myConnection.Close();
                return list;
            }
            else
            {
                myConnection.Close();
                list = null;
            }
        }
        catch
        {
            myConnection.Close();
            list = null;
        }
        return list;
    }

    public List<CategoryItem> ByAncestors(int Ancestor)
    {
        //OrderBy =0 order by numbers else order by name
        List<CategoryItem> list = new List<CategoryItem>();
        CategoryItem f;

        Dal.arParams.Clear();
        Dal.arParams.Add(Ancestor);
        DataTable DT = Dal.ExecuteDBsp("usp_GetCategoryByParent", Dal.arParams);
        if(DT!=null)
            if(DT.Rows.Count>0)
                foreach (DataRow DR in DT.Rows)
                {
                    f = new CategoryItem();
                    f.cPageName = Convert.ToString(DR["cPageName"]);
                    f.Ancestor = Convert.ToInt32(DR["Ancestor"]);
                    f.catId = Convert.ToInt32(DR["catId"]);
                    f.cTitle = Convert.ToString(DR["cTitle"]);
                    f.number = Convert.ToInt32(DR["orderID"]);
                    list.Add(f);
                }
        return list;
    }

    public List<CategoryItem> ByType(int ctypeId)
    {
        List<CategoryItem> list =new List<CategoryItem>();

        myConnection=new SqlConnection(connectionString);
        myCommand =new SqlCommand("usp_GetCategorysByType", myConnection);

        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@ctypeId", ctypeId);

        try
        {
            myConnection.Open();
            SqlDataReader reader = myCommand.ExecuteReader();
            CategoryItem f;
            if(reader.HasRows)
            {
                while(reader.Read())
                {
                    f = new CategoryItem();
                    f.cPageName = Convert.ToString(reader["cPageName"]);
                    f.Ancestor = Convert.ToInt32(reader["Ancestor"]);
                    f.catId = Convert.ToInt32(reader["catId"]);
                    f.ctypeId = Convert.ToInt32(reader["ctypeId"]);
                    f.cTitle = Convert.ToString(reader["cTitle"]);
                    // f.inSearch = CBool(reader("inSearch"))
                    f.number = Convert.ToInt32(reader["number"]);
                    list.Add(f);
                }
                reader.Close();
                myConnection.Close();
                return list;
            }
            else
            {
                myConnection.Close();
                list = null;
            }
        }
        catch
        {
            myConnection.Close();
            list = null;
        }
        return list;
    }
    
    public int IfcPageNameExsitNew(String cPageNam)
    {
        myConnection =new SqlConnection(connectionString);
        myCommand =new SqlCommand("usp_CheckIfcPageNameExsitNew", myConnection);

        // Mark the Command as a SPROC
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@cPageName", cPageNam);

        myConnection.Open();
        int Id;
        try
        {
            Id = Convert.ToInt32(myCommand.ExecuteScalar());
            myConnection.Close();
            return Id;
        }
        catch(Exception ex)
        {
            myConnection.Close();
            return 0;
        }
    }

    public bool IfcPageNameExsit(string cPageNam)
    {
        myConnection =new SqlConnection(connectionString);
        myCommand =new SqlCommand("usp_CheckIfcPageNameExsit", myConnection);

        // Mark the Command as a SPROC
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@cPageName", cPageNam);

        myConnection.Open();
        int Id;
        try
        {
            Id = Convert.ToInt32(myCommand.ExecuteScalar());
            myConnection.Close();
            if(Id== 0)
                return true;
            else
                return false;
        }
        catch(Exception ex) 
        {
            myConnection.Close();
            return false;
        }
    }

    public int UpdatePageName(int catId, string cPageName)
    {
        myConnection =new SqlConnection(connectionString);
        myCommand = new SqlCommand("usp_CategoryUpdatePagName", myConnection);

        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@catId", catId);
        myCommand.Parameters.AddWithValue("@cPageName", cPageName);

        myConnection.Open();
        int res;
        try
        {
            res = Convert.ToInt32(myCommand.ExecuteScalar());
            myConnection.Close();
            return res;
        }
        catch(Exception ex)
        {
            myConnection.Close();
            return -1;
        }
    }
    
    public int UpdateName(int catId, String cTitle ) 
    {
        myConnection =new SqlConnection(connectionString);
        myCommand =new SqlCommand("usp_CategoryUpdate", myConnection);

        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@catId", catId);
        myCommand.Parameters.AddWithValue("@cTitle", cTitle);

        myConnection.Open();
        int res;
        try
        {
            res = myCommand.ExecuteNonQuery();
            myConnection.Close();
            return res;
    }
        catch(Exception ex)
        {myConnection.Close();
            return -1;
    }
    }
    
    public int UpdateNameDescription(int catId, string cTitle, string cDescription)
    {

        myConnection = new SqlConnection(connectionString);
        myCommand = new SqlCommand("usp_CategoryUpdateDescription", myConnection);

        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@catId", catId);
        myCommand.Parameters.AddWithValue("@cTitle", cTitle);
        myCommand.Parameters.AddWithValue("@cDescription", cDescription);
        myConnection.Open();
        int res;
        try
        {
            res = myCommand.ExecuteNonQuery();
            myConnection.Close();
            return res;
        }
        catch (Exception ex )
        {   myConnection.Close();
            return -1;
        }
    }

    public int UpdateNumber(int catId, int number)
    {
        myConnection =new SqlConnection(connectionString);
        myCommand =new SqlCommand("usp_CategoryUpdateNumber", myConnection);

        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@catId", catId);
        myCommand.Parameters.AddWithValue("@number", number);

        myConnection.Open();
        int res ;
        try
        {
            res = myCommand.ExecuteNonQuery();
            myConnection.Close();
            return res;
        }
        catch (Exception ex)
        {
            myConnection.Close();
            return -1;
        }
    }


    public int InsertEncy(int Ancestor, string cTitle, string cPageName , int ctypeId , string cDescription) 
    {
        myConnection =new SqlConnection(connectionString);
        myCommand =new SqlCommand("usp_CategorysInserByTypeEncy", myConnection);

        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@Ancestor", Ancestor);
        myCommand.Parameters.AddWithValue("@cTitle", cTitle);
        myCommand.Parameters.AddWithValue("@cPageName", cPageName);
        myCommand.Parameters.AddWithValue("@ctypeId", ctypeId);
        myCommand.Parameters.AddWithValue("@cDescription", cDescription);
        myConnection.Open();
        int Id;
        try
        {
            Id = Convert.ToInt32(myCommand.ExecuteScalar());
            myConnection.Close();
            return Id;
        }
        catch(Exception ex)
        {    myConnection.Close();
            return 0;
        }
    }

}

[Serializable] 
public class CategoryItem
{
    public CategoryItem()
    {
    }

    private String _cDescription;

    public String cDescription 
    {
       get
       {    
           return _cDescription;
       }
        set
        {
            _cDescription = value;
        }
    }

    private int _catId;
    
    public int catId
    {
        get
        {
            return _catId;
        }
        set
        {
            _catId = value;
        }
    }

    private int _Number; 
    public int number
    {
        get
        {
            return _Number;
        }
        set
        {
            _Number = value;
        }
    }
    private int _ctypeId;
    public int ctypeId
    {
        get
        {
            return _ctypeId;
        }
        set
        {
            _ctypeId = value;
        }
    }

    private string _cTitle;

    public string cTitle
    {
        get
        {
            return _cTitle;
        }
        set
    {
            _cTitle = value;
        }
    }

    private String _cPageName;
    public String cPageName
    {
        get
        {
            return _cPageName;
        }
        set
        {
            _cPageName = value;
        }
    }

    private int _Ancestor;
    public int Ancestor
    {
        get
        {
            return _Ancestor;
        }
        set
    {
            _Ancestor = value;
    }
    }

    private bool _inSearch;
    public bool inSearch
    {
        get
        {
            return _inSearch;
        }
        set
        {
            _inSearch = value;
        }
    }
}

public class HandFinderCategoty
{
    private string keyword;
    public HandFinderCategoty(string lookingCategory)
{
    keyword = lookingCategory;
}
    public bool Match(CategoryItem item)
    {
        return item.cTitle.Contains(keyword);
        //Return item.cTitle.Contains(item.cPageName.IndexOf(keyword, 0))
}
}

public class HandFinderKeysbyId
{private string keyword ;
    public HandFinderKeysbyId(string x)
    {
        keyword = x;
    }

    public bool Match(CategoryItem item)
    {    //'  Return item.KeyId = x
        return item.cTitle.ToLower().StartsWith(keyword);
    }
}

